package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_CYXJX;

public class GetEMR_CYXJXData {
	
	private static final Log log = LogFactory.getLog(GetEMR_CYXJXData.class);
	public static List<EMR_CYXJX> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_CYXJX> re = new ArrayList<EMR_CYXJX>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        
	        String sql = "select "
	        		+"'' as HZZSYH,"
					+"a.yljgdm as YLJGZZJGDM,"
					+"'' as  YLJGMC,"
					+"a.zyh as ZYH,"
					+"a.xm as XM,"
					+"a.xb as XB,"
					+"a.nl as NL,"
					+"to_char(b.csny,'YYYY-MM-dd HH24:mi:ss') as CSRQ,"
					+"b.jzd as XXXDZ,"
					+"b.gzdw as GZDW,"
					+"'' as CSD,"
					+"b.mz as MZDM,"
					+"b.hyzk as HYZKDM,"
					+"'' as ZYLBDM,"
					+"a.zyysgh as ZYYSGH,"
					+"a.zyysxm as ZYYSMC,"
					+"'1990-01-01 00:00:00' as ZYYSQMSJ,"
					+"a.zzysgh as ZZYSGH,"
					+"a.zzysxm as ZZYSQM,"
					+"'1990-01-01 00:00:00' as ZZYSQMSJ,"
					+"'' as CYXJLX,"
					+"to_char(a.rysj,'YYYY-MM-dd HH24:mi:ss') as RYRQSJ,"
					+"to_char(a.cysj,'YYYY-MM-dd HH24:mi:ss') as CYRQSJ,"
					+"c.ryksbm as RYKSDM,"
					+"c.ryksmc as RYKSMC,"
					+"a.ch as BCH,"
					+"a.zyts as SJZYTS,"
					+"'' as RYZDDM,"
					+"a.ryzd as RYZD,"
					+"'' as RYQK,"
					+"a.zlgc as ZLGCMS,"
					+"a.cyqk as CYQK,"
					+"'' as CYZDDM,"
					+"a.cyzd as CYZD,"
					+"a.cyyz as CYYZ,"
					+"'' as ZLJGDM,"
					+"'' as YXFZJCJG,"
					+"a.zyszgcjg as ZYSZGCJGMS,"
					+"a.ssgc as SSGCMS,"
					+"a.zzzf as ZZZF,"
					+"a.zyjzff as ZYJZFF,"
					+"a.zyyyff as ZYYYFF,"
					+"a.cyqk as CYSZZYTZ,"
	
					+"to_char(a.ywscsj,'YYYY-MM-dd HH24:mi:ss') as BCRQ,"
					+"'' as ZRLSH,"
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from tb_zy_cyxj a left join tb_ba_jbxx b "
					+" on a.kh = b.kh and a.klx = b.klx"
					+" left join tb_zy_rydjb c on a.yljgdm = c.yljgdm"
					+" and a.kh = c.kh and a.klx = c.klx ";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_CYXJX temp = new EMR_CYXJX();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setYLJGMC(result.getString("YLJGMC"));
	        	temp.setZYH(result.getString("ZYH"));
	        	temp.setXM(result.getString("XM"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setNL(result.getString("NL"));
	        	temp.setCSRQ(result.getString("CSRQ"));
	        	temp.setXXXDZ(result.getString("XXXDZ"));
	        	temp.setGZDW(result.getString("GZDW"));
	        	temp.setCSD(result.getString("CSD"));
	        	temp.setMZDM(result.getString("MZDM"));
	        	temp.setHYZKDM(result.getString("HYZKDM"));
	        	temp.setZYLBDM(result.getString("ZYLBDM"));
	        	
	        	temp.setZYYSGH(result.getString("ZYYSGH"));
	        	temp.setZYYSMC(result.getString("ZYYSMC"));
	        	temp.setZYYSQMSJ(result.getString("ZYYSQMSJ"));
	        	temp.setZZYSGH(result.getString("ZZYSGH"));
	        	temp.setZZYSQM(result.getString("ZZYSQM"));
	        	temp.setZZYSQMSJ(result.getString("ZZYSQMSJ"));
	        	temp.setCYXJLX(result.getString("CYXJLX"));
	        	temp.setRYRQSJ(result.getString("RYRQSJ"));
	        	temp.setCYRQSJ(result.getString("CYRQSJ"));
	        	temp.setRYKSDM(result.getString("RYKSDM"));
	        	temp.setRYKSMC(result.getString("RYKSMC"));
	        	temp.setBCH(result.getString("BCH"));
	        	temp.setSJZYTS(result.getString("SJZYTS"));
	        	temp.setRYZDDM(result.getString("RYZDDM"));
	        	temp.setRYZD(result.getString("RYZD"));
	        	temp.setRYQK(result.getString("RYQK"));
	        	temp.setZLGCMS(result.getString("ZLGCMS"));
	        	temp.setCYQK(result.getString("CYQK"));
	        	temp.setCYZDDM(result.getString("CYZDDM"));
	        	temp.setCYZD(result.getString("CYZD"));
	        	temp.setCYYZ(result.getString("CYYZ"));
	        	temp.setZLJGDM(result.getString("ZLJGDM"));
	        	temp.setYXFZJCJG(result.getString("YXFZJCJG"));
	        	temp.setZYSZGCJGMS(result.getString("ZYSZGCJGMS"));
	        	temp.setSSGCMS(result.getString("SSGCMS"));
	        	temp.setZZZF(result.getString("ZZZF"));
	        	temp.setZYJZFF(result.getString("ZYJZFF"));
	        	temp.setZYYYFF(result.getString("ZYYYFF"));
	        	temp.setCYSZZYTZ(result.getString("CYSZZYTZ"));

	        	temp.setBCRQ(result.getString("BCRQ"));
	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
